package com.api.generation.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.crypto.SecureUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.json.JSONObject;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.api.generation.constants.ApiGenConstants;
import com.api.generation.domain.*;
import com.api.generation.mapper.ApiGenAccessMapper;
import com.api.generation.mapper.ApiGenDictDataMapper;
import com.api.generation.mapper.ApiGenExecuteMapper;
import com.api.generation.mapper.ApiGenInfoMapper;
import com.api.generation.service.IApiGenService;
import com.api.generation.util.ApiGenCacheUtils;
import com.ruoyi.common.core.exception.ServiceException;
import com.ruoyi.common.core.utils.DateUtils;
import com.ruoyi.common.core.utils.StringUtils;
import com.ruoyi.common.core.utils.uuid.IdUtils;
import com.ruoyi.common.core.web.domain.AjaxResult;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.Cleanup;
import lombok.extern.log4j.Log4j2;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据服务通用接口Service业务层处理
 * 
 * @author gaochao
 * @date 2023-07-02
 */
@Log4j2
@Service
public class ApiGenServiceImpl implements IApiGenService
{
    @Autowired
    private ApiGenInfoMapper apiGenInfoMapper;
    @Autowired
    private ApiGenExecuteMapper apiGenExecuteMapper;
    @Autowired
    private ApiGenAccessMapper apiGenAccessMapper;
    @Autowired
    private ApiGenDictDataMapper apiGenDictDataMapper;

    // 支持多种数据库
    private final Map<String, DataSource> dataSourceMap = new HashMap<>();


    /**
     * 项目启动时，初始化 api服务信息、api执行、api出入参、api字典数据表到缓存
     */
    @PostConstruct
    public void init()
    {
        resetApiGenInfoCache();
        resetApiGenExecuteCache();
        resetApiGenAccessCache();
        resetApiGenDictCache();
        // 初始化数据源
        initDataSource();
    }

    private void initDataSource() {
        // 初始化数据库连接实例并保存
        List<ApiGenDictData> apiGenDictCache = getApiGenDictCache(ApiGenConstants.DATA_SOURCE_TYPE);
        if(null==apiGenDictCache){
            log.error("没有可初始化的数据源");
            return;
        }
        for (ApiGenDictData apiGenDictData:apiGenDictCache) {
            // 状态（0正常 1停用）
            String status = apiGenDictData.getStatus();
            if(ApiGenConstants.DEACTIVATE_STATUS.equals(status)){
                continue;
            }
            String dataSoureStr = apiGenDictData.getDictValue();
            com.alibaba.fastjson.JSONObject jsonObject = JSON.parseObject(dataSoureStr);
            String databaseType = jsonObject.getString("databaseType");
            String host = jsonObject.getString("host");
            Integer port = Integer.valueOf(jsonObject.getString("port"));
            String database = jsonObject.getString("database");
            String username = jsonObject.getString("username");
            String password = jsonObject.getString("password");
            DataSource dataSource = null;
            try {
                dataSource = configureDataSource(databaseType, host, port, database, username, password);
            } catch (Exception e) {
                e.printStackTrace();
                log.error("{}连接异常:{}", databaseType,e.getMessage());
                continue;
            }
            this.dataSourceMap.put(SecureUtil.md5(databaseType.toLowerCase(Locale.ROOT)), dataSource);
        }
    }
        /**
         * 初始化数据源
         * @return
         */
    public DataSource initDataSource(String dbtype) {
        // 获取数据库连接实例
        if(StringUtils.isNotEmpty(dbtype)){
            dbtype = dbtype.toLowerCase(Locale.ROOT);
            String key = SecureUtil.md5(dbtype);
            DataSource dataSource = this.dataSourceMap.get(key);
            if (dataSource != null) {
                return dataSource;
            }else{
                // 初始化数据库连接实例并保存
                List<ApiGenDictData> apiGenDictCache = getApiGenDictCache(ApiGenConstants.DATA_SOURCE_TYPE);
                if(null==apiGenDictCache){
                    throw new ServiceException(dbtype+":未启用或未配置数据源,请先到字典管理启用或配置");
                }
                for (ApiGenDictData apiGenDictData:apiGenDictCache) {
                    // 状态（0正常 1停用）
                    String status = apiGenDictData.getStatus();
                    String dictLabel = apiGenDictData.getDictLabel();
                    if(dbtype.equals(dictLabel.toLowerCase(Locale.ROOT))){
                        if(ApiGenConstants.DEACTIVATE_STATUS.equals(status)){
                            throw new ServiceException(dbtype+":未启用,请先到字典管理启用");
                        }
                        String dataSoureStr = apiGenDictData.getDictValue();
                        com.alibaba.fastjson.JSONObject jsonObject = JSON.parseObject(dataSoureStr);
                        String databaseType = jsonObject.getString("databaseType");
                        String host = jsonObject.getString("host");
                        Integer port = Integer.valueOf(jsonObject.getString("port"));
                        String database = jsonObject.getString("database");
                        String username = jsonObject.getString("username");
                        String password = jsonObject.getString("password");
                        try {
                            dataSource = configureDataSource(databaseType, host, port, database, username, password);
                            this.dataSourceMap.put(SecureUtil.md5(databaseType.toLowerCase(Locale.ROOT)), dataSource);
                            return dataSource;
                        } catch (Exception e) {
                            e.printStackTrace();
                            log.error("{}连接异常:{}", databaseType,e.getMessage());
                            throw new ServiceException(databaseType+"连接异常:"+e.getMessage());
                        }
                    }
                }
                if(dataSource == null){
                    throw new ServiceException(dbtype+":暂未开放,敬请期待");
                }
                return null;
            }
        }else{
            throw new IllegalArgumentException("非法参数异常");
        }
    }

    public DataSource debugDataSource(String dbtype) {
        // 获取数据库连接实例
        if(StringUtils.isNotEmpty(dbtype)){
            DataSource dataSource = null;
            dbtype = dbtype.toLowerCase(Locale.ROOT);
            // 初始化数据库连接实例并保存
            List<ApiGenDictData> apiGenDictCache = getApiGenDictCache(ApiGenConstants.DATA_SOURCE_TYPE);
            for (ApiGenDictData apiGenDictData:apiGenDictCache) {
                // 状态（0正常 1停用）
                String status = apiGenDictData.getStatus();
                String dictLabel = apiGenDictData.getDictLabel();
                if(dbtype.equals(dictLabel.toLowerCase(Locale.ROOT))){
                    String dataSoureStr = apiGenDictData.getDictValue();
                    com.alibaba.fastjson.JSONObject jsonObject = JSON.parseObject(dataSoureStr);
                    String databaseType = jsonObject.getString("databaseType");
                    String host = jsonObject.getString("host");
                    Integer port = Integer.valueOf(jsonObject.getString("port"));
                    String database = jsonObject.getString("database");
                    String username = jsonObject.getString("username");
                    String password = jsonObject.getString("password");
                    if(ApiGenConstants.DEACTIVATE_STATUS.equals(status)){
                        this.dataSourceMap.remove(SecureUtil.md5(databaseType.toLowerCase(Locale.ROOT)));
                        throw new ServiceException(dbtype+":未启用,请先到字典管理启用");
                    }
                    try {
                        dataSource = configureDataSource(databaseType, host, port, database, username, password);
                        this.dataSourceMap.put(SecureUtil.md5(databaseType.toLowerCase(Locale.ROOT)), dataSource);
                        return dataSource;
                    } catch (Exception e) {
                        this.dataSourceMap.remove(SecureUtil.md5(databaseType.toLowerCase(Locale.ROOT)));
                        e.printStackTrace();
                        log.error("{}连接异常:{}", databaseType,e.getMessage());
                        throw new ServiceException(databaseType+"连接异常:"+e.getMessage());
                    }
                }
            }
            if(dataSource == null){
                throw new ServiceException(dbtype+":暂未开放,敬请期待");
            }
            return null;
        }else{
            throw new IllegalArgumentException("非法参数异常");
        }
    }

    private DataSource configureDataSource(String databaseType, String host, int port, String database, String username, String password) {
        HikariConfig config = new HikariConfig();
        String url;
        if (databaseType.equals("mysql")) {
            url = "jdbc:mysql://" + host + ":" + port + "/" + database;
        } else if (databaseType.equals("clickhouse")) {
            url = "jdbc:clickhouse://" + host + ":" + port + "/" + database+"?compress=0";
            // 上面的连接字符串中，compress=1 表示启用压缩，而 compress_algorithm=gzip 表示使用 Gzip 压缩算法。
        } else if (databaseType.equals("opengauss")) {
            url = "jdbc:opengauss://" + host + ":" + port + "/" + database;
        } else {
            throw new IllegalArgumentException("Unsupported database type: " + databaseType);
        }
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        config.setMaximumPoolSize(10); // 设置最大连接数 默认值是 10
        config.setMinimumIdle(5); // 设置最小空闲连接数 默认值是 10
        HikariDataSource hikariDataSource = new HikariDataSource(config);
        return hikariDataSource;
    }

    @Override
    public AjaxResult getDBType(String dbtype) {
        // 获取数据库连接实例
        if (StringUtils.isNotEmpty(dbtype)) {
            dbtype = dbtype.toLowerCase(Locale.ROOT);
            DataSource dataSource = initDataSource(dbtype);
            if (dataSource != null) {
                String querySql="";
                if (dbtype.equals("mysql")) {
                    querySql = " SELECT schema_name AS dbname" +
                            " FROM information_schema.schemata" +
                            " WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');";
                } else if (dbtype.equals("clickhouse")) {

                    querySql = " SELECT name AS dbname" +
                            " FROM system.databases WHERE name NOT IN ('default', 'system', 'graphite', 'prestodb');";
                } else if (dbtype.equals("opengauss")) {
                    querySql=" SELECT schema_name as dbname" +
                            " FROM information_schema.schemata" +
                            " WHERE schema_name not in ('pg_catalog','information_schema','pg_toast');";
                } else {
                    throw new IllegalArgumentException("Unsupported database type: " + dbtype);
                }
                try {
                    List<Entity> query = Db.use(dataSource).query(querySql, MapUtil.newHashMap());
                    if(query==null || query.size()==0){
                        return AjaxResult.error("未查询到数据库");
                    }
                    return AjaxResult.success(query);
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new IllegalArgumentException("根据数据库类型获取数据库中的库名异常:"+e.getMessage());
                }
            }else{
                return AjaxResult.error(dbtype+":暂未开放,敬请期待");
            }
        }else{
            throw new IllegalArgumentException("非法参数异常");
        }
    }

    @Override
    public AjaxResult getDBTypeDBname(String dbtype, String dbname) {
        // 获取数据库连接实例
        if (StringUtils.isNotEmpty(dbtype)) {
            dbtype = dbtype.toLowerCase(Locale.ROOT);
            DataSource dataSource = initDataSource(dbtype);
            if (dataSource != null) {
                String querySql="";
                if (dbtype.equals("mysql")) {
                    // Tables_in_nacos_config
                    querySql = String.format(" SELECT table_name AS name " +
                            " FROM information_schema.tables " +
                            " WHERE table_schema = '%s';",dbname);
                } else if (dbtype.equals("clickhouse")) {
                    // name
                    querySql = String.format("SHOW TABLES FROM %s;",dbname);
                } else if (dbtype.equals("opengauss")) {
                    // name
                    querySql=String.format(" SELECT table_name as name" +
                            " FROM information_schema.tables" +
                            " WHERE table_schema = '%s';",dbname);
                } else {
                    throw new IllegalArgumentException("Unsupported database type: " + dbtype);
                }
                try {
                    List<Entity> query = Db.use(dataSource).query(querySql, MapUtil.newHashMap());
                    if(query==null || query.size()==0){
                        return AjaxResult.error("未查询到表名");
                    }
                    return AjaxResult.success(query);
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new IllegalArgumentException("根据数据库类型和数据库名获取数据库中的表名异常:"+e.getMessage());
                }
            }else{
                return AjaxResult.error(dbtype+":暂未开放,敬请期待");
            }
        }else{
            throw new IllegalArgumentException("非法参数异常");
        }
    }


    @Override
    public AjaxResult getDBTypeDBnameTablename(String dbtype, String dbname, String tablename) {
        // 获取数据库连接实例
        if (StringUtils.isNotEmpty(dbtype)) {
            dbtype = dbtype.toLowerCase(Locale.ROOT);
            DataSource dataSource = initDataSource(dbtype);
            if (dataSource != null) {
                String querySql="";
                if (dbtype.equals("mysql")) {
                    // columnname
                    querySql = String.format(" SELECT COLUMN_NAME AS columnname " +
                            " FROM INFORMATION_SCHEMA.COLUMNS " +
                            " WHERE TABLE_NAME = '%s' AND TABLE_SCHEMA = '%s';",tablename,dbname);
                } else if (dbtype.equals("clickhouse")) {
                    // columnname
                    querySql = String.format(" SELECT name AS columnname " +
                            "  FROM system.columns " +
                            "  WHERE database = '%s' " +
                            "  AND table = '%s';",dbname,tablename);
                } else if (dbtype.equals("opengauss")) {
                    // columnname
                    querySql=String.format(" SELECT column_name as columnname" +
                            " FROM information_schema.columns" +
                            " WHERE table_schema = '%s'" +
                            " AND table_name = '%s';",dbname,tablename);
                } else {
                    throw new IllegalArgumentException("Unsupported database type: " + dbtype);
                }
                try {
                    List<Entity> query = Db.use(dataSource).query(querySql, MapUtil.newHashMap());
                    if(query==null || query.size()==0){
                        return AjaxResult.error("未查询到字段");
                    }
                    return AjaxResult.success(query);
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new IllegalArgumentException("根据数据库类型和数据库名和表名获取表中的字段异常:"+e.getMessage());
                }
            }else{
                return AjaxResult.error(dbtype+":暂未开放,敬请期待");
            }
        }else{
            throw new IllegalArgumentException("非法参数异常");
        }
    }
    /**
     * 加载api服务信息缓存数据
     */
    @Override
    public void loadingApiGenInfoCache()
    {
        ApiGenInfo apiGenInfo = new ApiGenInfo();
        apiGenInfo.setStatus("0");
        List<ApiGenInfo> apiGenInfos = apiGenInfoMapper.selectApiGenInfoList(apiGenInfo);
        for (ApiGenInfo entry : apiGenInfos)
        {
            ApiGenCacheUtils.setApiGenInfoCache(entry.getApiPath(),entry);
        }
    }

    /**
     * 加载api执行缓存数据
     */
    @Override
    public void loadingApiGenExecuteCache()
    {
        ApiGenExecute apiGenExecute = new ApiGenExecute();
        apiGenExecute.setStatus("0");
        List<ApiGenExecute> apiGenExecutes = apiGenExecuteMapper.selectApiGenExecuteList(apiGenExecute);
        for (ApiGenExecute entry : apiGenExecutes)
        {
            ApiGenCacheUtils.setApiGenExecuteCache(entry.getId().toString(),entry);
        }
    }

    /**
     * 加载api出入参缓存数据
     */
    @Override
    public void loadingApiGenAccessCache()
    {
        ApiGenAccess apiGenAccess = new ApiGenAccess();
        apiGenAccess.setStatus("0");
        List<ApiGenAccess> ApiGenAccesss = apiGenAccessMapper.selectApiGenAccessList(apiGenAccess);
        for (ApiGenAccess entry : ApiGenAccesss)
        {
            ApiGenCacheUtils.setApiGenAccessCache(entry.getId().toString(),entry);
        }
    }

    /**
     * 加载api字典数据缓存数据
     */
    @Override
    public void loadingApiGenDictDataCache()
    {
        ApiGenDictData apiGenaDictData = new ApiGenDictData();
        apiGenaDictData.setStatus("0");
        Map<String, List<ApiGenDictData>> dictDataMap = apiGenDictDataMapper.selectApiGenDictDataList(apiGenaDictData).stream().collect(Collectors.groupingBy(ApiGenDictData::getDictType));
        for (Map.Entry<String, List<ApiGenDictData>> entry : dictDataMap.entrySet())
        {
            ApiGenCacheUtils.setApiGenDictCache(entry.getKey(), entry.getValue().stream().sorted(Comparator.comparing(ApiGenDictData::getDictSort)).collect(Collectors.toList()));
        }
    }

    /**
     * 获取api服务信息缓存数据
     */
    @Override
    public ApiGenInfo getApiGenInfoCache(String id)
    {
        ApiGenInfo apiGenInfoCache = ApiGenCacheUtils.getApiGenInfoCache(id);
        return apiGenInfoCache;
    }

    /**
     * 获取api执行缓存数据
     */
    @Override
    public ApiGenExecute getApiGenExecuteCache(String id)
    {
        ApiGenExecute cache = ApiGenCacheUtils.getApiGenExecuteCache(id);
        if(null == cache){
            cache = apiGenExecuteMapper.selectApiGenExecuteById(Long.valueOf(id));
            ApiGenCacheUtils.setApiGenExecuteCache(id,cache);
        }
        return cache;
    }

    /**
     * 获取api出入参缓存数据
     */
    @Override
    public ApiGenAccess getApiGenAccessCache(String id)
    {
        ApiGenAccess cache = ApiGenCacheUtils.getApiGenAccessCache(id);
        if(null == cache){
            cache = apiGenAccessMapper.selectApiGenAccessById(Long.valueOf(id));
            ApiGenCacheUtils.setApiGenAccessCache(id,cache);
        }
        return cache;
    }


    /**
     * 获取api字典数据缓存数据
     */
    @Override
    public List<ApiGenDictData> getApiGenDictCache(String id)
    {
        List<ApiGenDictData> cache = ApiGenCacheUtils.getApiGenDictCache(id);
        return cache;
    }

    /**
     * 删除api服务信息缓存数据
     */
    @Override
    public void removeApiGenInfoCache(String apiPath)
    {
         ApiGenCacheUtils.removeApiGenInfoCache(apiPath);
    }

    /**
     * 删除api执行缓存数据
     */
    @Override
    public void removeApiGenExecuteCache(String id)
    {
        ApiGenCacheUtils.removeApiGenExecuteCache(id);
    }

    /**
     * 删除api出入参缓存数据
     */
    @Override
    public void removeApiGenAccessCache(String id)
    {
        ApiGenCacheUtils.removeApiGenAccessCache(id);
    }

    /**
     * 删除api字典数据缓存数据
     */
    @Override
    public void removeApiGenDictCache(String dictType)
    {
        ApiGenCacheUtils.removeApiGenDictCache(dictType);
    }

    /**
     * 清空api服务信息缓存数据
     */
    @Override
    public void clearApiGenInfoCache()
    {
        ApiGenCacheUtils.clearApiGenInfoCache();
    }

    /**
     * 清空api出入参信息缓存数据
     */
    @Override
    public void clearApiGenAccessCache()
    {
        ApiGenCacheUtils.clearApiGenAccessCache();
    }

    /**
     * 清空api执行缓存数据
     */
    @Override
    public void clearApiGenExecuteCache()
    {
        ApiGenCacheUtils.clearApiGenExecuteCache();
    }

    /**
     * 清空api字典数据缓存数据
     */
    @Override
    public void clearApiGenDictCache()
    {
        ApiGenCacheUtils.clearApiGenDictCache();
    }

    /**
     * 重置api服务信息缓存数据
     */
    @Override
    public void resetApiGenInfoCache()
    {
        clearApiGenInfoCache();
        loadingApiGenInfoCache();
    }

    /**
     * 重置api执行缓存数据
     */
    @Override
    public void resetApiGenExecuteCache()
    {
        clearApiGenExecuteCache();
        loadingApiGenExecuteCache();
    }

    /**
     * 重置api出入参缓存数据
     */
    @Override
    public void resetApiGenAccessCache()
    {
        clearApiGenAccessCache();
        loadingApiGenAccessCache();
    }

    /**
     * 重置api字典数据缓存数据
     */
    @Override
    public void resetApiGenDictCache()
    {
        clearApiGenDictCache();
        loadingApiGenDictDataCache();
    }

    @Override
    public AjaxResult createImpl(JSONObject parameterFromBody, ApiGenInfo apiGenInfoCache, ApiGenAccess apiGenAccessCache, ApiGenExecute apiGenExecuteCache) {
        // 1.校验入参
        AjaxResult ajaxResult = verifyInputParameters(parameterFromBody, apiGenAccessCache);
        log.info("校验入参：{}",ajaxResult.toString());

        // 1.1 判断校验是否通过
        boolean success = ajaxResult.isSuccess();
        if(!success){
            return ajaxResult;
        }
        // 1.2 获取到字段和值
        HashMap<String, Object> stringTHashMap = (HashMap<String, Object>)ajaxResult.get(AjaxResult.DATA_TAG);

        // 2.拼装sql
        String updateField = apiGenExecuteCache.getUpdateField();
        // 数据库类型
        String databaseType = apiGenExecuteCache.getDatabaseType();
        // 数据库名
        String databaseName = apiGenExecuteCache.getDatabaseName();
        // 表名
        String tableName = apiGenExecuteCache.getTableName();
        // 获取新增字段
        List<ApiUpdateField> apiUpdateFields = JSONArray.parseArray(updateField, ApiUpdateField.class);

        // 新增列名
        StringBuilder fieldStringBuilder = new StringBuilder();
        // 新增列值
        StringBuilder valueStringBuilder = new StringBuilder();

        for (ApiUpdateField apiUpdateField : apiUpdateFields)
        {   // 字段名称
            String fieldName = apiUpdateField.getFieldName();
            // 参数类型
            Integer valueSource = apiUpdateField.getValueSource();
            // 是否主键
//            Integer isPrimary = apiUpdateField.getIsPrimary();
            // 字段类型
//            String parameterType = apiUpdateField.getParameterType();
//            if(1==isPrimary && 1!=valueSource && 4!=valueSource && 6!=valueSource){
//                return AjaxResult.warn("主键字段的值来源支持如下几种:api入参、系统UUID、数据库自增");
//            }
            // 参数
            String parameter = apiUpdateField.getParameter();
//            Object o = null;
            switch (valueSource) {
                case 1: // api入参
                    fieldStringBuilder.append(",`"+fieldName+"`");
                    Object t = stringTHashMap.get(parameter);
                    if (null==t) {
                        return AjaxResult.badrequest("入参"+ parameter+",缺少");
                    }else if (t instanceof String) {
                        // parameterValue是String类型
                        valueStringBuilder.append(",'"+t+"'");
                    }else{
                        valueStringBuilder.append(","+t);
                    }
                    break;
//                case 2:// 系统时间(yyyy-MM-dd HH:mm:ss)
//                    fieldStringBuilder.append(",`"+fieldName+"`");
//                    try {
//                        o = customParameterType(parameterType, DateUtils.getTime());
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        valueStringBuilder.append(",'"+o+"'");
//                    }else{
//                        valueStringBuilder.append(","+o );
//                    }
//                    break;
//                case 3: // 系统日期(yyyy-MM-dd)
//                    fieldStringBuilder.append(",`"+fieldName+"`");
//                    try {
//                        o = customParameterType(parameterType, DateUtils.getDate());
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        valueStringBuilder.append(",'"+o+"'");
//                    }else{
//                        valueStringBuilder.append(","+o );
//                    }
//                    break;
//                case 4: // 系统UUID
//                    fieldStringBuilder.append(",`"+fieldName+"`");
//                    try {
//                        o = customParameterType(parameterType, IdUtils.simpleUUID());
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        valueStringBuilder.append(",'"+o+"'");
//                    }else{
//                        valueStringBuilder.append(","+o );
//                    }
//                    break;
//                case 5: // 自定义
//                    fieldStringBuilder.append(",`"+fieldName+"`");
//                    try {
//                        o = customParameterType(parameterType, parameter);
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        valueStringBuilder.append(",'"+o+"'");
//                    }else{
//                        valueStringBuilder.append(","+o );
//                    }
//                    break;
//                case 6:// 数据库自增
//                    break;
//                case 7:// 设置为空值(NULL)
//                    fieldStringBuilder.append(",`"+fieldName+"`");
//                    valueStringBuilder.append(","+null);
//                    break;
//                case 8:// 系统时间(yyyyMMddHHmmss)
//                    fieldStringBuilder.append(",`"+fieldName+"`");
//                    try {
//                        o = customParameterType(parameterType, DateUtils.dateTimeNow());
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        valueStringBuilder.append(",'"+o+"'");
//                    }else{
//                        valueStringBuilder.append(","+o );
//                    }
//                    break;
//                case 9:// 系统时间(yyyyMMdd)
//                    fieldStringBuilder.append(",`"+fieldName+"`");
//                    try {
//                        o = customParameterType(parameterType, DateUtils.getDateYYYYMMDD());
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        valueStringBuilder.append(",'"+o+"'");
//                    }else{
//                        valueStringBuilder.append(","+o );
//                    }
//                    break;
                default:
                    log.info("字段:{},参数类型:{} 非法参数",fieldName,valueSource);
                    return AjaxResult.warn("字段:"+fieldName+",参数类型:"+valueSource+"非法参数");
            }
        }

        String fieldString = fieldStringBuilder.substring(1);
        String valueString = valueStringBuilder.substring(1);
        String strSql = String.format(ApiGenConstants.INSERTAPIGEN, databaseName,tableName,fieldString,valueString);

        //2.1 sql防注入
        AjaxResult ajaxResultSql = sqlInjectionPrevention(strSql, ApiGenConstants.CREATE);
        boolean successSql = ajaxResultSql.isSuccess();
        if(!successSql){
            return ajaxResultSql;
        }

        // 获取当前系统的换行符
        String s = System.lineSeparator();
        log.info("新增sql语句参数 :{} {} 参数值 :{}", fieldString,s,valueString);
        log.info("新增sql语句 :{}", strSql);

        // 3.执行slq返回执行结果
        return insertUpdateByDatabaseType(databaseType,strSql);
    }

    private AjaxResult insertUpdateByDatabaseType(String databaseType, String sql) {
        DataSource dataSource = initDataSource(databaseType);
        int rows = 0;
        try {
            if(databaseType.toLowerCase(Locale.ROOT).equals("clickhouse")){
                // 获取连接
                Connection connection = dataSource.getConnection();
                // 创建 PreparedStatement 对象
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                rows = preparedStatement.executeUpdate();
                log.info("clickhouse执行sql语句是否成功"+rows);
            }else if(databaseType.toLowerCase(Locale.ROOT).equals("opengauss")){
                // opengauss sql语句不需要"`"
                sql = sql.replace("`", "");
                rows = Db.use(dataSource).execute(sql);
                log.info("执行sql语句影响的行数"+rows);
            }else{
                rows = Db.use(dataSource).execute(sql);
                log.info("执行sql语句影响的行数"+rows);
            }
            return rows > 0 ? AjaxResult.success() : AjaxResult.error();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return AjaxResult.error("执行sql语句异常:"+throwables.getMessage());
        }
    }

    /**
     * 校验入参类型和是否必传参数
     * @param parameterFromBody
     * @param apiGenAccessCache
     * @return
     */
    private AjaxResult verifyInputParameters(JSONObject parameterFromBody, ApiGenAccess apiGenAccessCache) {

        HashMap<String, Object> stringTHashMap = new HashMap<>();

        String enteringGinseng = apiGenAccessCache.getEnteringGinseng();
        List<ApiEnteringGinseng> apiEnteringGinsengs = JSONArray.parseArray(enteringGinseng, ApiEnteringGinseng.class);
        for (ApiEnteringGinseng apiEnteringGinseng : apiEnteringGinsengs)
        {
            // 获取入参字段
            String parameter = apiEnteringGinseng.getParameter();
            // 获取入参是否必填
            Integer isMandatory = apiEnteringGinseng.getIsMandatory();
            // 获取入参类型
            String parameterType = apiEnteringGinseng.getParameterType();
            // 获取入参的值
            Object parameterValue =  parameterFromBody.get(parameter);
            // 是否包含这个入参
            boolean boolparameter = parameterFromBody.containsKey(parameter);

            // 入参是否必填校验
            if(ApiGenConstants.IS_REQUIRED.equals(isMandatory) && !boolparameter){
                return AjaxResult.badrequest("入参"+ parameter+",缺少");
            }
            if(!boolparameter){
                // 没有传递此参数也是非必填参数
                continue;
            }
            // 入参类型校验
            switch (parameterType.trim().toLowerCase(Locale.ROOT)){
                case ApiGenConstants.STRING:
                    if (parameterValue instanceof String) {
                        stringTHashMap.put(parameter,parameterValue);
                    }else{
                        return AjaxResult.badrequest("入参"+ parameter+",类型错误");
                    }
                    break;
                case ApiGenConstants.INTEGER:
                     if (parameterValue instanceof Integer) {
                        stringTHashMap.put(parameter,parameterValue);
                    }else{
                        return AjaxResult.badrequest("入参"+ parameter+",类型错误");
                    }
                    break;
                case ApiGenConstants.DOUBLE:
                    if (parameterValue instanceof Double) {
                        stringTHashMap.put(parameter,parameterValue);
                    }else{
                        return AjaxResult.badrequest("入参"+ parameter+",类型错误");
                    }
                    break;
                case ApiGenConstants.FLOAT:
                    if (parameterValue instanceof Float) {
                        stringTHashMap.put(parameter,parameterValue);
                    }else{
                        return AjaxResult.badrequest("入参"+ parameter+",类型错误");
                    }
                    break;
                default:
                    log.info("不支持的数据或媒体类型");
                    return AjaxResult.unsupportedtype("不支持的数据或媒体类型");
            }
        }
        return AjaxResult.success(stringTHashMap);
    }

    @Override
    public AjaxResult updateImpl(JSONObject parameterFromBody, ApiGenInfo apiGenInfoCache, ApiGenAccess apiGenAccessCache, ApiGenExecute apiGenExecuteCache) {
        // 1.校验入参
        AjaxResult ajaxResult = verifyInputParameters(parameterFromBody, apiGenAccessCache);
        log.info("校验入参：{}",ajaxResult.toString());

        // 1.1 判断校验是否通过
        boolean success = ajaxResult.isSuccess();
        if(!success){
            return ajaxResult;
        }
        // 1.2 获取到字段和值
        HashMap<String, Object> stringTHashMap = (HashMap<String, Object>)ajaxResult.get(AjaxResult.DATA_TAG);

        // 2.拼装sql
        // 2.1 获取更新字段json格式类型
        String updateField = apiGenExecuteCache.getUpdateField();
        // 2.2 获取条件json格式类型
        String conditional = apiGenExecuteCache.getConditional();

        // 数据库类型
        String databaseType = apiGenExecuteCache.getDatabaseType();
        // 数据库名
        String databaseName = apiGenExecuteCache.getDatabaseName();
        // 表名
        String tableName = apiGenExecuteCache.getTableName();

        // 获取更新字段
        List<ApiUpdateField> apiUpdateFields = JSONArray.parseArray(updateField, ApiUpdateField.class);
        // 更新列
        StringBuilder fieldStringBuilder = new StringBuilder();

        for (ApiUpdateField apiUpdateField : apiUpdateFields)
        {   // 字段名称
            String fieldName = apiUpdateField.getFieldName();
            // 参数类型
            Integer valueSource = apiUpdateField.getValueSource();
            // 参数
            String parameter = apiUpdateField.getParameter();
            // 是否主键
//            Integer isPrimary = apiUpdateField.getIsPrimary();
            // 参数类型
//            String parameterType = apiUpdateField.getParameterType();
//            if(1==isPrimary && 6==valueSource){
//                return AjaxResult.warn("修改字段的值来源不支持:数据库自增");
//            }
//            Object o;
            switch (valueSource) {
                case 1: // api入参
                    fieldStringBuilder.append(", `"+fieldName+"` = ");
                    Object t = stringTHashMap.get(parameter);
                    if (t instanceof String) {
                        // parameterValue是String类型
                        fieldStringBuilder.append("'"+t+"'");
                    }else{
                        fieldStringBuilder.append(t);
                    }
                    break;
//                case 2:// 系统时间(yyyy-MM-dd HH:mm:ss)
//                    fieldStringBuilder.append(", `"+fieldName+"` = ");
//                    try {
//                        o = customParameterType(parameterType, DateUtils.getTime());
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        fieldStringBuilder.append("'"+o+"'");
//                    }else{
//                        fieldStringBuilder.append(o);
//                    }
//                    break;
//                case 3: // 系统日期(yyyy-MM-dd)
//                    fieldStringBuilder.append(", `"+fieldName+"` = ");
//                    try {
//                        o = customParameterType(parameterType, DateUtils.getDate());
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        fieldStringBuilder.append("'"+o+"'");
//                    }else{
//                        fieldStringBuilder.append(o );
//                    }
//                    break;
//                case 4: // 系统UUID
//                    fieldStringBuilder.append(", `"+fieldName+"` = ");
//                    try {
//                        o = customParameterType(parameterType, IdUtils.simpleUUID());
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        fieldStringBuilder.append("'"+o+"'");
//                    }else{
//                        fieldStringBuilder.append(o );
//                    }
//                    break;
//                case 5: // 自定义
//                    fieldStringBuilder.append(", `"+fieldName+"` = ");
//                    try {
//                        o = customParameterType(parameterType, parameter);
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        fieldStringBuilder.append("'"+o+"'");
//                    }else{
//                        fieldStringBuilder.append(o );
//                    }
//                    break;
//                case 7:// 设置为空值(NULL)
//                    fieldStringBuilder.append(", `"+fieldName+"` = "+null);
//                    break;
//                case 8:// 系统时间(yyyyMMddHHmmss)
//                    fieldStringBuilder.append(", `"+fieldName+"` = ");
//                    try {
//                        o = customParameterType(parameterType, DateUtils.dateTimeNow());
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        fieldStringBuilder.append("'"+o+"'");
//                    }else{
//                        fieldStringBuilder.append(o );
//                    }
//                    break;
//                case 9:// 系统时间(yyyyMMdd)
//                    fieldStringBuilder.append(", `"+fieldName+"` = ");
//                    try {
//                        o = customParameterType(parameterType, DateUtils.getDateYYYYMMDD());
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    if (o instanceof String) {
//                        fieldStringBuilder.append("'"+o+"'");
//                    }else{
//                        fieldStringBuilder.append(o );
//                    }
//                    break;
                default:
                    log.info("字段:{},参数类型:{} 非法参数",fieldName,valueSource);
                    return AjaxResult.warn("字段:"+fieldName+",参数类型:"+valueSource+"非法参数");
            }
        }
        // 条件字段
        List<ApiConditionalField> apiConditionalFields = JSONArray.parseArray(conditional, ApiConditionalField.class);
        // 条件列
        StringBuilder conditionalStringBuilder = new StringBuilder();

        for (ApiConditionalField apiConditionalField : apiConditionalFields)
        {   // 字段名称
            String fieldName = apiConditionalField.getFieldName();
            // 参数类型
            Integer valueSource = apiConditionalField.getValueSource();
            // 字段类型暂时没用上
//            String parameterType = apiConditionalField.getParameterType();
            // api字段
            String parameter = apiConditionalField.getParameter();
            // 条件判断
            Integer conditionType = apiConditionalField.getConditionType();
            if(conditionType==null){
                return AjaxResult.warn("条件字段"+fieldName+"判断条件不能为空");
            }
            // 逻辑运算
            String logicalType = apiConditionalField.getLogicalType();
            if(StringUtils.isEmpty(logicalType)){
                return AjaxResult.warn("条件字段"+fieldName+"逻辑运算不能为空");
            }
            if(valueSource!=null && 1!=valueSource && 5!=valueSource){
                return AjaxResult.warn("条件字段"+fieldName+"的值来源只支持:api入参,自定义");
            }

            if(conditionalStringBuilder.length()==0){
                // 第一次条件不加逻辑运算符
            }else{
                conditionalStringBuilder.append(" "+logicalType);
            }
            // 逻辑运算有几种情况可以跳过后面的逻辑
            // 9:IS NULL
            // 10:IS NOT NULL
            if(conditionType<1 || conditionType>13){
                String format = StringUtils.format("字段:{},条件类型:{} 非法参数", parameter, conditionType);
                log.info(format);
                return AjaxResult.warn(format);
            }else if(9==conditionType){
                conditionalStringBuilder.append(" `"+fieldName+"` IS NULL");
                continue;
            }else if(10==conditionType){
                conditionalStringBuilder.append(" `"+fieldName+"` IS NOT NULL");
                continue;
            }

            conditionalStringBuilder.append(" `"+fieldName+"` ");
            switch (valueSource) {
                case 1: // api入参
                    Object t = stringTHashMap.get(parameter);
                    conditionalJudgment(conditionType,conditionalStringBuilder,t);
                    break;
//                case 5: // 自定义
//                    Object o = null;
//                    try {
//                        o = customParameterType(parameterType, parameter);
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                        return AjaxResult.badrequest("字段"+ fieldName+",转换错误:"+e.getMessage());
//                    }
//                    conditionalJudgment(conditionType,conditionalStringBuilder,o);
//                    break;
                default:
                    log.info("字段:{},参数类型:{} 非法参数",fieldName,valueSource);
                    return AjaxResult.warn("字段:"+fieldName+",参数类型:"+valueSource+"非法参数");
            }
        }
        String strSql;
        String fieldString=null;
        if(fieldStringBuilder.length()>0){
             fieldString = fieldStringBuilder.substring(1);
        }else{
            return AjaxResult.warn("api:"+apiGenInfoCache.getApiPath()+"未设置更新字段");
        }
        if(conditionalStringBuilder.length()>0){
            strSql = String.format(ApiGenConstants.UPDATEAPIGEN, databaseName,tableName,fieldString,conditionalStringBuilder);
        }else{
            //strSql = String.format(ApiGenConstants.UPDATEAPIGENNOWHERE, databaseName,tableName,fieldString);
            return AjaxResult.warn("api:"+apiGenInfoCache.getApiPath()+"未设置更新条件");
        }
        //2.1 sql防注入
        AjaxResult ajaxResultSql = sqlInjectionPrevention(strSql, ApiGenConstants.UPDATE);
        boolean successSql = ajaxResultSql.isSuccess();
        if(!successSql){
            return ajaxResultSql;
        }

        // 获取当前系统的换行符
        String s = System.lineSeparator();
        log.info("更新语句sql参数 :{} {} 条件值 :{}", fieldString,s,conditionalStringBuilder);
        log.info("更新语句sql:{}", strSql);
        // 3.执行slq返回执行结果
        return insertUpdateByDatabaseType(databaseType,strSql);
    }



    /**
     *
     * 自定义值来源类型校验
     * @param parameterType
     * @param parameter
     */
    private Object customParameterType(String parameterType, String parameter) throws Exception {
        // 自定义值来源类型校验
        switch (parameterType.trim().toLowerCase(Locale.ROOT)){
            case ApiGenConstants.STRING:
                return String.valueOf(parameter);
            case ApiGenConstants.INTEGER:
                return Integer.valueOf(parameter);
            case ApiGenConstants.DOUBLE:
                return Double.valueOf(parameter);
            case ApiGenConstants.FLOAT:
                return Float.valueOf(parameter);
            default:
                throw new Exception("非法的报表后缀值类型");
        }
    }

    /**
     * sql防注入
     * @param action
     * @param strSql
     */
    private AjaxResult sqlInjectionPrevention(String strSql, String action) {
        String upperSql = strSql.toUpperCase(Locale.ROOT).trim();
        log.info("转换为大写的原始Sql:"+upperSql);
        if(upperSql.contains("DROP")){
            return AjaxResult.error("非法SQL包含‘DROP’关键字");
        }else if(upperSql.contains("TABLE")){
            return AjaxResult.error("非法SQL包含‘TABLE’关键字");
        }else if(upperSql.contains("ALTER")){
            return AjaxResult.error("非法SQL包含‘ALTER’关键字");
        }else if(upperSql.contains(";")){
            return AjaxResult.error("非法SQL包含‘;’字符");
        }
        switch (action.trim().toLowerCase(Locale.ROOT)){
            case ApiGenConstants.CREATE:
                if(upperSql.contains("DELETE")){
                    return AjaxResult.error("入参中不能包含‘DELETE’关键字");
                }else if(upperSql.contains("SELECT")){
                    return AjaxResult.error("入参中不能包含‘SELECT’关键字");
                }else if(upperSql.contains("UPDATE")){
                    return AjaxResult.error("入参中不能包含‘UPDATE’关键字");
                }
                break;
            case ApiGenConstants.UPDATE:
                if(upperSql.contains("DELETE")){
                    return AjaxResult.error("入参中不能包含‘DELETE’关键字");
                }else if(upperSql.contains("SELECT")){
                    return AjaxResult.error("入参中不能包含‘SELECT’关键字");
                }else if(upperSql.contains("CREATE")){
                    return AjaxResult.error("入参中不能包含‘CREATE’关键字");
                }
                break;
            default:
                if(upperSql.startsWith("DELETE")){
                    return AjaxResult.error("包含‘DELETE’关键字");
                }else if(upperSql.startsWith("UPDATE")){
                    return AjaxResult.error("包含‘UPDATE’关键字");
                }else if(upperSql.startsWith("CREATE")){
                    return AjaxResult.error("包含‘CREATE’关键字");
                }
        }
        return AjaxResult.success();
    }

    /**
     * 加上条件运算符和值
     * @param conditionType
     * @param conditionalStringBuilder
     * @param t
     */
    private void conditionalJudgment(Integer conditionType, StringBuilder conditionalStringBuilder, Object t) {
        switch (conditionType) {
            case 1: // 等于
                if (t instanceof String) {
                    conditionalStringBuilder.append(" = '"+t+"'");
                }else{
                    conditionalStringBuilder.append(" = "+t);
                }
                break;
            case 2: // 不等于
                if (t instanceof String) {
                    conditionalStringBuilder.append(" != '"+t+"'");
                }else{
                    conditionalStringBuilder.append(" != "+t);
                }
                break;
            case 3: // 大于
                conditionalStringBuilder.append(" > "+t);
                break;
            case 4: // 小于
                conditionalStringBuilder.append(" < "+t);
                break;
            case 5: // 大于等于
                conditionalStringBuilder.append(" >= "+t);
                break;
            case 6: // 小于等于
                conditionalStringBuilder.append(" <= "+t);
                break;
            case 7: // 包含
                conditionalStringBuilder.append(" IN("+t+")");
                break;
            case 8: // 不包含
                conditionalStringBuilder.append(" NOT IN("+t+")");
                break;
            case 11: // 全模糊
                conditionalStringBuilder.append(" LIKE '%"+t+"%'");
                break;
            case 12: // 左模糊
                conditionalStringBuilder.append(" LIKE '%"+t+"'");
                break;
            case 13: // 右模糊
                conditionalStringBuilder.append(" LIKE '"+t+"%'");
                break;
        }

    }

    @Override
    public AjaxResult selectImpl(JSONObject parameterFromBody, ApiGenInfo apiGenInfoCache, ApiGenAccess apiGenAccessCache, ApiGenExecute apiGenExecuteCache) {
        // 1.校验入参
        AjaxResult ajaxResult = verifyInputParameters(parameterFromBody, apiGenAccessCache);
        log.info("校验入参：{}",ajaxResult.toString());

        // 1.1 判断校验是否通过
        boolean suc = ajaxResult.isSuccess();
        if(!suc){
            return ajaxResult;
        }
        // 1.2 获取到字段和值
        HashMap<String, Object> stringTHashMap = (HashMap<String, Object>)ajaxResult.get(AjaxResult.DATA_TAG);

        // 2.拼装sql
        // sql入参
        Map<String, Object> paramMap = MapUtil.newHashMap();
        String strSql = apiGenExecuteCache.getSqlStatement();
        // 数据库类型
        String databaseType = apiGenExecuteCache.getDatabaseType();
        Set<String> strings = stringTHashMap.keySet();
        for (String str : strings)
        {
            Object t = stringTHashMap.get(str);
            paramMap.put(str,t);
            strSql=strSql.replace("#{"+str+"}"," @"+str+" ");
        }

        //2.1 sql防注入
        AjaxResult ajaxResultSql = sqlInjectionPrevention(strSql, ApiGenConstants.SELECT);
        boolean successSql = ajaxResultSql.isSuccess();
        if(!successSql){
            return ajaxResultSql;
        }

        // 获取当前系统的换行符
        log.info("sql防注入验证后的sql语句:{}", strSql);

        // 2.2 校验是否待分页查询条件
        Number count=0; // 总条数
        String countSql;
        AjaxResult ajaxResultPage = null;
        String selectSql = String.format("select * from (%s)result ",strSql);
        boolean LIMIT = strSql.contains("LIMIT");
        boolean limit = strSql.contains("limit");
        if(LIMIT){
            String[] limits = strSql.split("LIMIT");
            countSql = String.format("select count(1) as con from (%s)totalnumber ", limits[0]);
            ajaxResultPage = pageStrFilterate(limits, paramMap);

        }else if(limit){
            String[] limits = strSql.split("limit");
            countSql = String.format("select count(1) as con from (%s)totalnumber ", limits[0]);
            ajaxResultPage = pageStrFilterate(limits,paramMap);
        }else{
            // 如果sql语句没有分页参数系统默认加上分页参数
            countSql = String.format("select count(1) as con from (%s)totalnumber ", strSql);
            strSql=strSql+" LIMIT " +(ApiGenConstants.PAGENUM-1)*ApiGenConstants.PAGESIZE+","+ApiGenConstants.PAGESIZE;
            selectSql = String.format("select *  from (%s)result ",strSql);
        }
        // 分页参数结果校验
        boolean sucPage =ajaxResultPage==null?true:ajaxResultPage.isSuccess();
        if(!sucPage){
            return ajaxResultPage;
        }

        // 3.执行slq
        DataSource dataSource = initDataSource(databaseType);
        try {
            //总条数
            log.info("执行查询总条数语句countSql :{}", countSql);
            log.info("执行查询总条数语句countSql参数 :{}", paramMap.toString());
            count = Db.use(dataSource).queryNumber(countSql, paramMap);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.error("执行查询总条数sql异常:"+e.getMessage());
        }
        // 4.返回执行结果
        if (count.intValue() > 0) {
            try {
                log.info("执行查询语句selectSql :{}", selectSql);
                log.info("执行查询语句selectSql参数 :{}", paramMap.toString());
                List<Entity> query = Db.use(dataSource).query(selectSql, paramMap);
                // 4.1 过滤返回结果
                if(query==null||query.size()==0){
                    return AjaxResult.success(new ArrayList<>(),0);
                }
                // 4.2 获取出参字段
                String exoticGinseng = apiGenAccessCache.getExoticGinseng();
                List<ApiExoticField> apiEnteringGinsengs = JSONArray.parseArray(exoticGinseng, ApiExoticField.class);
                List<Entity> filteredResults = new ArrayList<>();
                for (Entity entity : query) {
                    Entity filteredEntity = new Entity();
                    for (ApiExoticField field : apiEnteringGinsengs) {
                        String fieldName = field.getParameter();
                        // 假设 Entity 类中有一个名为 getFieldValue() 的方法用于获取字段值
                        Object fieldValue = entity.get(fieldName);
                        filteredEntity.set(fieldName, fieldValue);
                    }
                    filteredResults.add(filteredEntity);
                }
                return AjaxResult.success(filteredResults, count.longValue());
            } catch (Exception e) {
                e.printStackTrace();
                return AjaxResult.error("执行查询语句selectSql异常:"+e.getMessage());
            }
        } else {
            return AjaxResult.success(new ArrayList<>(),count.longValue());
        }
    }

    /**
     * 分页参数校验
     * @param limits
     * @param paramMap
     * @return
     */
    private AjaxResult pageStrFilterate(String[] limits, Map<String, Object> paramMap) {
        String pageStr = null;
        Integer page = null;
        String pagesizeStr = null;
        Integer pagesize = null;
        try {
            String limitSql = limits[1];
            String[] split = limitSql.split(",");
            pageStr = split[0].trim().replace("@", "");
            page = (Integer)paramMap.get(pageStr);
            pagesizeStr = split[1].trim().replace("@", "");
            pagesize = (Integer)paramMap.get(pagesizeStr);
            if (page < 1) {
                return AjaxResult.badrequest("分页数:"+ pageStr+",不能小于1");
            }else if(pagesize < 1){
                return AjaxResult.badrequest("每页条数:"+ pagesizeStr+",不能小于1");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.badrequest("分页参数传参或sql语法错误"+e.getMessage());
        }
        paramMap.put(pageStr,(page-1)*pagesize);
        paramMap.put(pagesizeStr,pagesize);
        return AjaxResult.success();
    }

    @Override
    public AjaxResult exportImpl(JSONObject parameterFromBody, ApiGenInfo apiGenInfoCache, ApiGenAccess apiGenAccessCache, ApiGenExecute apiGenExecuteCache, HttpServletResponse response) {
        // 1.校验入参
        AjaxResult ajaxResult = verifyInputParameters(parameterFromBody, apiGenAccessCache);
        log.info("校验入参：{}",ajaxResult.toString());

        // 1.1 判断校验是否通过
        boolean suc = ajaxResult.isSuccess();
        if(!suc){
            return ajaxResult;
        }
        // 1.2 获取到字段和值
        HashMap<String, Object> stringTHashMap = (HashMap<String, Object>)ajaxResult.get(AjaxResult.DATA_TAG);

        // 2.拼装sql
        // sql入参
        Map<String, Object> paramMap = MapUtil.newHashMap();
        String strSql = apiGenExecuteCache.getSqlStatement();
        // 数据库类型
        String databaseType = apiGenExecuteCache.getDatabaseType();
        Set<String> strings = stringTHashMap.keySet();
        for (String str : strings)
        {
            Object t = stringTHashMap.get(str);
            paramMap.put(str,t);
            strSql=strSql.replace("#{"+str+"}"," @"+str+" ");
        }

        //2.1 sql防注入
        //2.1 sql防注入
        AjaxResult ajaxResultSql = sqlInjectionPrevention(strSql, ApiGenConstants.EXPORT);
        boolean successSql = ajaxResultSql.isSuccess();
        if(!successSql){
            return ajaxResultSql;
        }
        // 获取当前系统的换行符
        log.info("sql防注入验证后的sql语句:{}", strSql);

        // 2.2 校验是否待分页查询条件
        AjaxResult ajaxResultPage = null;
        boolean LIMIT = strSql.contains("LIMIT");
        boolean limit = strSql.contains("limit");
        int exportispag;
        if(LIMIT){
            String[] limits = strSql.split("LIMIT");
            ajaxResultPage = pageStrFilterate(limits, paramMap);
            exportispag=1;
        }else if(limit){
            String[] limits = strSql.split("limit");
            ajaxResultPage = pageStrFilterate(limits,paramMap);
            exportispag=1;
        }else{
            exportispag=0;
        }
        // 分页参数结果校验
        boolean sucPage =ajaxResultPage==null?true:ajaxResultPage.isSuccess();
        if(!sucPage){
            return ajaxResultPage;
        }
        // 导出格式校验
        String reportNameFormat = apiGenAccessCache.getReportNameFormat();
        ApiReportConfig apiReportConfig = com.alibaba.fastjson.JSONObject.parseObject(reportNameFormat, ApiReportConfig.class);
        if(null==apiReportConfig || apiReportConfig.getValueSource()==null || apiReportConfig.getFixedValue().isEmpty() || apiReportConfig.getFileType().isEmpty()){
            return AjaxResult.error("报表文件名称或文件类型不能为空");
        }
        // 3.或者执行数据源
        DataSource dataSource = initDataSource(databaseType);
        // 4.1 获取导出字段
        String exoticGinseng = apiGenAccessCache.getExoticGinseng();
        List<ApiExoticField> apiEnteringGinsengs = JSONArray.parseArray(exoticGinseng, ApiExoticField.class);
        List<String> parameters = new ArrayList<>();
        List<String> parameterNames = new ArrayList<>();
        for (ApiExoticField field : apiEnteringGinsengs) {
            String parameter = field.getParameter();
            String parameterName = field.getParameterName();
            parameters.add(parameter);
            parameterNames.add(parameterName);
        }
        if(parameterNames==null){
            return AjaxResult.error("报表表头不能为空");
        }
        try {
            downloadReport(response, apiReportConfig,exportispag,strSql,paramMap,dataSource,parameters,parameterNames);
        } catch (Exception e) {
            return AjaxResult.error("报表导出异常:"+e.getMessage());
        }
        return null;
    }

    /**
     * 报表导出
     * @param response
     * @param apiReportConfig
     * @param exportispag
     * @param sql
     * @param paramMap
     * @param dataSource
     * @param parameters
     * @param parameterNames
     * @throws Exception
     */
    private void downloadReport(HttpServletResponse response,
                                ApiReportConfig apiReportConfig,
                                int exportispag,
                                String sql,Map<String, Object> paramMap,
                                DataSource dataSource,
                                List<String> parameters,
                                List<String> parameterNames
                                ) throws Exception {
        //设置响应头
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        // 固定值
        String fixedValue1 = apiReportConfig.getFixedValue();
        // 动态值
        Integer valueSource = apiReportConfig.getValueSource();
        // 文件类型
        String fileType = apiReportConfig.getFileType();
        String fixedValue2="";
        switch (valueSource){
            case 3: // 系统日期(yyyy-MM-dd)
                fixedValue2 = DateUtils.getDate();
                break;
            case 4: // 系统UUID
                fixedValue2 = IdUtils.simpleUUID();
                break;
            case 9:// 系统时间(yyyyMMdd)
                fixedValue2 =DateUtils.getDateYYYYMMDD();
                break;
            case 8:// 系统时间(yyyyMMddHHmmss)
                fixedValue2 = DateUtils.dateTimeNow();
                break;
            case 10:// 时间(yyyyMMddHHmm)
                fixedValue2 = DateUtils.yyyyMMddHHmm();
                break;
            case 11:// 时间(yyyyMMddHH)
                fixedValue2 = DateUtils.yyyyMMddHH();
                break;
            case 12:// 月份(yyyyMM)
                fixedValue2 = DateUtils.yyyyMM();
                break;
            case 13:// 年份(yyyy)
                fixedValue2 = DateUtils.yyyy();
                break;
            default:
                throw new Exception("不支持的数据或媒体类型");
        }
        // 设置导出文件名
        String fileName = java.net.URLEncoder.encode(fixedValue1+fixedValue2+"."+fileType, "utf-8");
        log.info("导出文件名和格式：{}", fileName);
        response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

        //初始化表格
        @Cleanup OutputStream out = response.getOutputStream();
        @Cleanup PrintWriter printWriter = new PrintWriter(out);
        // 设置BOM信息
        printWriter.append(new String(new byte[] { (byte) 0xEF, (byte) 0xBB,(byte) 0xBF }));
        // 暂时写入表头
        printWriter.append(CollUtil.join(parameterNames, ",")).append("\n");
        printWriter.flush();
        // 自带导出条数限制的
        if(exportispag==1){
            String exportsql = String.format("select *  from (%s)result ",sql);
            log.info("自带导出条数限制,执行导出语句exportsql :{}", exportsql);
            log.info("自带导出条数限制,执行导出语句exportsql参数 :{}", paramMap.toString());
            List<Entity> query = Db.use(dataSource).query(exportsql, paramMap);
            // 4.2 过滤返回结果
            if(query==null||query.size()==0){
                return;
            }
            // 4.2.2 循环写入数据
            for (Entity entity : query ) {
                List<String> rowIndex = new LinkedList<>();
                for (String parameter : parameters) {
                    String str = entity.getStr(parameter);
                    if (StrUtil.isNotBlank(str)) {
                        rowIndex.add(str);
                    } else {
                        rowIndex.add("");
                    }
                }
                //暂时写入一部分
                printWriter.append(CollUtil.join(rowIndex, ",")).append("\n");
                printWriter.flush();
            }
        }else{
            // 没带导出条数限制
            int pageNum = 0;
            while (true) {
                pageNum++;
                // 如果sql语句没有分页参数系统默认加上分页参数
                String selectSql=sql+" LIMIT " +(pageNum-1)*ApiGenConstants.EXPORTPAGESIZE+","+ApiGenConstants.EXPORTPAGESIZE;
                String exportsql = String.format("select *  from (%s)result ",selectSql);

                log.info("没带导出条数限制,执行导出语句exportsql :{}", exportsql);
                log.info("没带导出条数限制,执行导出语句exportsql参数 :{}", paramMap.toString());
                List<Entity> query = Db.use(dataSource).query(exportsql, paramMap);
                // 4.1 过滤返回结果
                if(query==null||query.size()==0){
                    break;
                }
                // 4.1.2 循环写入数据
                for (Entity entity : query ) {
                    List<String> rowIndex = new LinkedList<>();
                    for (String parameter : parameters) {
                        String str = entity.getStr(parameter);
                        if (StrUtil.isNotBlank(str)) {
                            rowIndex.add(str);
                        } else {
                            rowIndex.add("");
                        }
                    }
                    //暂时写入一部分
                    printWriter.append(CollUtil.join(rowIndex, ",")).append("\n");
                    printWriter.flush();
                }
            }
        }
        printWriter.flush();
    }
}
